/**
 * DBaseFactory.java
 *
 * Copyright (C) 2009,  Tomislav Milkovic
 *
 * This program is free software; you can redistribute it and/or
 * modify it under the terms of the GNU General Public License
 * as published by the Free Software Foundation; either version 2
 * of the License, or (at your option) any later version.
 *
 * This program is distributed in the hope that it will be useful,
 * but WITHOUT ANY WARRANTY; without even the implied warranty of
 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
 * GNU General Public License for more details.
 *
 * You should have received a copy of the GNU General Public License
 * along with this program. if not, write to the Free Software
 * Foundation, Inc., 59 Temple Place - Suite 330, Boston, MA  02111-1307, USA.
 */
package extjsdyntran.translation.store.db;
import java.sql.*;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;


public  class DBaseFactory {

	private static final String dbPathStandAlone = "jdbc:hsqldb:file:";
    //private static final String dbPathStandAlone = "jdbc:hsqldb:mem:";
    private static final String dbPathWithServer = "jdbc:hsqldb:hsql://";

    static Connection con;

    private static PreparedStatement prstGetLastId;
    private static PreparedStatement prstShootDown;

    final static protected Logger logger = LoggerFactory.getLogger(DBaseFactory.class);
    

   public static boolean getInstance(String dbName) {
	    if(con!=null){
	    	try {
				if (!con.isClosed())   	return true;
			} catch (SQLException e) {
				e.printStackTrace();
			}
	    }
	    logger.info("Starting up database in stand alone mode...");
        
        loadDriver();
        try {
            con = DriverManager.getConnection(dbPathStandAlone + dbName, "sa", "");
            logger.info("Database started up!");
        }
        catch (SQLException e) {
            int code = e.getErrorCode();
            logger.error("Connecting to database failed: {} ", code ,e);
            throw new IllegalStateException("Cannot connect to database in stand alone mode.");
        }
        createTables();
        return true;
    }

    public static boolean getInstance(String dbName, String host) {
	    if(con!=null){
	    	try {
				if (!con.isClosed())   	return true;
			} catch (SQLException e) {
				e.printStackTrace();
			}
	    }
	    logger.info("Starting up database in server mode...");
        loadDriver();
        try {
            con = DriverManager.getConnection(dbPathWithServer + host + "/" + dbName, "sa", "");
            logger.info("Database started up! Connection successful!");
        }
        catch (SQLException e) {
            int code = e.getErrorCode();
            logger.error("Connecting to database failed: {} ", code ,e);
            throw new IllegalStateException("Cannot connect to DB server.");
        }
        createTables();
        return true;
    }

    
    private static void loadDriver() {
        try {
            Class.forName("org.hsqldb.jdbcDriver");
            logger.info("HSQLdb driver correctly loaded.");
        }
        catch (Exception e) {
            logger.error("Problem loading JDBC driver: ", e);
            throw new IllegalStateException("HSQLDB driver couldn't be loadded.");
        }
    }


    private static void createTables() {

        try {
            prstGetLastId = con.prepareStatement("call identity()");
            prstShootDown = con.prepareStatement("SHUTDOWN COMPACT");
        } 
        catch (SQLException e) {
            logger.error("Problem preparing databse.",e);
            throw new IllegalStateException("Problem preparing databse");
        }        

    }

    public static Connection getConnection(){
    	return con;
    }
    
    
    public static void ShootDown(){
        try {
        	prstShootDown.execute();            
        }
        catch (SQLException e) {
            int code = e.getErrorCode();
            logger.error("Closing down database returns error code {}",  code ,e);
        }
    	
    }
    
    /** Share method to get a resultSet from an SQL string */
    public static  ResultSet queryExecute(String sqlString) {
        try {
            PreparedStatement stmt = con.prepareStatement(sqlString);
            return queryExecute(stmt);
        }
        catch (SQLException e) {
            String  code = Integer.toString(e.getErrorCode());
            logger.error("Error executing statement {} returns error code {}",new Object[]{ sqlString ,code },e);
            return null;
        }
    }
    
    /** General method to execute a query using a PreparedStatement */
    public static ResultSet queryExecute(PreparedStatement stmt) {
        try {
            ResultSet result = stmt.executeQuery();
            return result;       
        }
        catch (SQLException e) {
        	logger.error("Error executing statement {} ",stmt,e);
            return null;        
        }                  
    }

    /** General method to do a statement.execute from a String. Returns the number of rows affected  */
    public static int statementExecute(String sql)
    {
        int nb = -1;
        try {
            PreparedStatement stmt = con.prepareStatement(sql);        // on prepare le statement
            nb = statementExecute(stmt);
        }
        catch (SQLException e) {
        	logger.error("Error executing statement {} ",sql,e);
        }
        logger.info("Executed statement {} returns {}",  sql , nb);
        return nb;
    }

    /** General method to do a statementExecute from a PreparedStatement. Returns the number of rows affected */
    public static int statementExecute(PreparedStatement stmt)
    {
        int nb = -1;
        try {
            nb = stmt.executeUpdate();                        // la commande passee en parametre
        }
        catch (SQLException e) {
            String code = Integer.toString(e.getErrorCode());
            logger.error("Error executing statement {} returns error code {}",new Object[]{ stmt,code },e);
        }
        logger.info("Executed statement {} returns {}",  stmt , nb);
        return nb;
    }



    /** Returns the Id of the last element inserted into the DB */
    public static  int getIdentity() {
        ResultSet rs = queryExecute(prstGetLastId);
        try {
            rs.next();
            int id = rs.getInt(1);
            rs.close();
            return id;
        }
        catch (SQLException e) {
        	logger.error("Error getting next record ID",e);
            throw new IllegalStateException("Error getting next record ID.");
        }
    }    
    
}
